************************************************************************************************************
* ULC: Unit labour cost
************************************************************************************************************

clear
set more off

global rootfolder "L:\DRS\Users\BurcuE\RAuer_ALS\ALS_data_and_replication_files"
global ulcfolder "$rootfolder\data\5_ULC"
capture mkdir "$ulcfolder\output"
cd "$ulcfolder\output"

************************************************************************************************************
* OECD
************************************************************************************************************

global OECD = 1

if $OECD == 1 {
insheet using "$ulcfolder/ULC_EEQ_21072016164837103.csv",clear

keep location time value

split time, parse("-") gen(stub)
destring stub*, ignore("Q") replace

rename stub1 year
rename stub2 quarter
gen date_q = yq(year, quarter)
format %tq date_q

replace value = value/100
rename value ulc_yoy

rename location iso3
keep  iso3 ulc_yoy year quarter date_q

gen source = "oecd"
save "ULC_OECD_yoy.dta", replace
}
*end of OECD section


************************************************************************************************************
* Eurostat data (eurostat bulk download, serie namq_aux_ulc)
************************************************************************************************************
global EUROSTAT = 1
if $EUROSTAT == 1 {

clear
insheet using "$ulcfolder\namq_aux_ulc.tsv", tab

split v1, parse(",") gen(stub)
drop v1
order stub*
replace stub4 = "geo" if stub4=="geo\time"
foreach var of varlist _all {
	local name = `var'[1]
	rename `var' y`name'
}

drop in 1
rename yindic_na indic_na
rename yunit unit
rename ys_adj s_adj
rename ygeo geo

keep if indic_na == "NULC"
drop indic_na

keep if s_adj == "NSA"
drop s_adj
drop if geo=="EU15"
drop if geo=="EU27"
drop if geo=="EU28"
drop if geo=="EA12"
drop if geo=="EA17"
drop if geo=="EA18"
drop if geo=="EA"
replace geo = "GB" if geo =="UK"
replace geo = "GR" if geo =="EL"

destring y*, ignore(": b p d e") replace

*keep percentage change YOY (that is percentage change since same quarter previous year)
keep if unit == "PCH_SAME"
drop unit
reshape long  y, i(geo) j(bla) string

split bla, parse("Q") gen(stub)
drop bla
destring stub*, replace
gen date_q = yq(stub1, stub2)
format %tq date_q

rename stub1 year
rename stub2 quarter
rename y ulc_yoy
drop if mi(ulc_yoy)

order geo date
sort geo date

rename geo iso2
merge m:1 iso2 using "$rootfolder\data\0_Concordances&OtherAuxiliaryFiles\output\0.0_concordances_names.dta", keepusing(iso3) 
drop _merge

drop if mi(ulc_yoy)
replace ulc_yoy = ulc_yoy/100
gen source = "estat"

save "ulc_eurostat_yoy.dta", replace
}
*end eurostat



************************************************************************************************************
* BLS Data (http://download.bls.gov/pub/time.series/pr/)
************************************************************************************************************
global BLS=1

if $BLS ==1 {

clear
insheet using "$ulcfolder/pr.data.1.AllData", tab

destring period, ignore("Q") replace

*keep only the "business" serie, which is the widest
*and gives the percentage change YOY (so the 4quarter change)
keep if series_id == "PRS84006111"
drop if period==5

rename value ulc_yoy
replace ulc_yoy = ulc_yoy/100

keep year period ulc
rename period quarter
gen date_q = yq(year, quarter)
format %tq date_q

gen iso3 = "USA"
gen iso2 = "US"
gen source = "bls"

save "ulc_BLS_yoy.dta", replace

}
*end BLS


************************************************************************************************************
* Stats Canada
************************************************************************************************************
global StatsCan = 1

if $StatsCan == 1{

clear 
insheet using "$ulcfolder/03830008-eng.csv", comma

keep if geo=="Canada"
keep if com == "Unit labour cost"
keep if bus == "Business sector"

split ref_date, parse("/") gen(stub)

destring stub*, replace
gen quarter = 1 if stub2==3
replace quarter =2 if stub2==6
replace quarter =3 if stub2==9
replace quarter =4 if stub2==12

rename stub1 year

gen date_q = yq(year, quarter)
format %tq date_q

keep year quarter date_q value

tsset date_q

gen ulc_yoy = ln(value)-ln(l4.value)

keep year quarter date_q  ulc_yoy
gen iso2="CA"
gen iso3="CAN"

drop if mi(ulc_yoy)
gen source = "statcan"

save "ulc_StatsCan_yoy.dta", replace

}
*end stats canada


************************************************************************************************************
* Datastream
************************************************************************************************************
global DS = 1

if $DS == 1 {

clear 
import excel using "$ulcfolder/ULC_mx_tw_ro.xlsx"

rename A date
rename B ulc_MEX1
rename C ulc_MEX2
rename D ulc_TWN
rename E ulc_ROM

drop in 1

gen month = month(date)
gen year = year(date)
gen datemo = ym(year, month)
format %tm datemo

drop date

destring ulc*, replace

gen quarter = 1 if month >0
replace quarter = 2 if month >3
replace quarter = 3 if month >6
replace quarter = 4 if month >9
bys year quarter (datemo): keep if _n == _N

gen date_q = yq(year, quarter)
format %tq date_q

drop month datemo

tsset date_q
gen ulc_yoy_MEX1 = ln(ulc_MEX1) - ln(l4.ulc_MEX1)
gen ulc_yoy_MEX2 = ln(ulc_MEX2) - ln(l4.ulc_MEX2)
gen ulc_yoy_TWN = ln(ulc_TWN) - ln(l4.ulc_TWN)
gen ulc_yoy_ROM = ln(ulc_ROM) - ln(l4.ulc_ROM)

replace ulc_yoy_MEX2 = ulc_yoy_MEX1 if mi(ulc_yoy_MEX2)
drop ulc_yoy_MEX1
rename ulc_yoy_MEX2 ulc_yoy_MEX

drop ulc_MEX* ulc_TWN ulc_ROM
reshape long ulc_yoy_, i(date_q) j(iso3) string
rename ulc_yoy_ ulc_yoy
drop if mi(ulc_yoy)
gen source = "ds"

save "ulc_DS_yoy.dta", replace

}
*end of datastream


************************************************************************************************************
* Append all together
************************************************************************************************************
use "ulc_eurostat_yoy.dta",clear

* OECD has longer data for ESP, GRC, LTU, drop from estat- berik-
drop if iso3 == "ESP"
drop if iso3 == "GRC"
drop if iso3 == "LTU"
drop if iso3 == "ROM"
drop if iso3 == "ROU"

save "ulc_eurostat_yoy.dta",replace

levelsof iso3, local(ctryEurostat)
di `ctryEurostat'

clear
use "ULC_OECD_yoy.dta"

*drop countries that are in eurostat (if we have the national source, we don't use OECD)
foreach cc of local ctryEurostat {
	drop if iso3 == "`cc'"
}
drop if iso3 == "USA"
drop if iso3 == "CAN"

append using "ulc_eurostat_yoy.dta"
append using "$ulcfolder/output/ulc_BLS_yoy.dta"
append using "$ulcfolder/output/ulc_StatsCan_yoy.dta"
append using "$ulcfolder/output/ulc_DS_yoy.dta"

isid iso3 date_q
compress

rename iso3 country
keep country ulc_yoy year quarter date_q

drop if country=="EU15"
drop if country=="EU27"
drop if country=="EU28"
drop if country=="EA12"
drop if country=="EA17"
drop if country=="EA18"
drop if country=="EA19"

order country year quarter date_q ulc_yoy
sort country year quarter date_q

save "ulc_all_iso3_yoy.dta", replace
